package me.magicall.db.springjdbc;

import java.lang.reflect.InvocationTargetException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Collection;
import java.util.List;
import java.util.Map;

import me.magicall.db.dao.CommonDaoTemplate;
import me.magicall.db.meta.DbColumn;
import me.magicall.db.meta.TableMeta;
import me.magicall.db.meta.TableMetaAccessor;
import me.magicall.db.util.DbUtil;
import me.magicall.db.util.FieldType;
import me.magicall.db.util.ReturnKeyAndAffect;
import me.magicall.util.kit.Kit;
import me.magicall.util.kit.Kits;

import org.apache.commons.beanutils.PropertyUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcOperations;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;

/**
 * 一个与具体表无关的"静态"dao
 * 目前的限制：
 * model类和表的key必须是单字段，数据类型只能是Integer或Long
 * 
 * @author MaGiCalL
 * @param <T>
 */
public class SpringJdbcCommonDao<T> extends CommonDaoTemplate<T> {

	private TableMetaAccessor tableMetaAccessor;
	private NamedParameterJdbcOperations jdbc;

	public SpringJdbcCommonDao() {
		super();
	}

	public SpringJdbcCommonDao(final TableMetaAccessor tableMetaAccessor, final NamedParameterJdbcOperations jdbc) {
		super();
		this.tableMetaAccessor = tableMetaAccessor;
		this.jdbc = jdbc;
	}

//====================================== 方法区 methods ===========================================
	//----------------------------------读方法 query methods
	@Override
	protected Collection<T> fill(final String sql, final Map<Object, T> idMap, final Object[] ids) {
		final TableMeta tableMeta = getTableMetaUsingModel(idMap.get(String.valueOf(ids[0])));
		final RowMapper<T> rm = new RowMapper<T>() {
			@Override
			public T mapRow(final ResultSet resultSet, final int rowNum) throws SQLException {
				final Object idFromResultSet = DbUtil.getIdFromResultSet(resultSet, tableMeta);
				final T modelOnlyWithId = idMap.get(String.valueOf(idFromResultSet));
				return mappingRowWithoutId(modelOnlyWithId, resultSet, rowNum);
			}
		};
		return getJdbc().query(sql, new SqlParameterSource() {

			@Override
			public boolean hasValue(final String paramName) {
				return getValue(paramName) != null;
			}

			@Override
			public Object getValue(final String paramName) throws IllegalArgumentException {
				return ids[Kits.INT.fromString(paramName.substring(idFieldName().length()))];
			}

			@Override
			public String getTypeName(final String paramName) {
				return null;
			}

			@Override
			public int getSqlType(final String paramName) {
				return Integer.MIN_VALUE;
			}
		}, rm);
	}

	@Override
	protected T fill(final T modelOnlyWithId, final String sql) {
		try {
			return getJdbc().queryForObject(sql, buildSqlParameterSource(modelOnlyWithId), new RowMapper<T>() {
				@Override
				public T mapRow(final ResultSet resultSet, final int rowNum) throws SQLException {
					return mappingRowWithoutId(modelOnlyWithId, resultSet, rowNum);
				}
			});
		} catch (final EmptyResultDataAccessException e) {
			return null;
		}
	}

	protected T mappingRowWithoutId(final T modelOnlyWithId, final ResultSet resultSet, final int rowNum) throws SQLException {
		final TableMeta tableMeta = getTableMetaUsingModel(modelOnlyWithId);
		final ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
		final int columnCount = resultSetMetaData.getColumnCount();
		for (int i = DbUtil.RESULT_SET_COLUMN_START_INDEX; i <= columnCount; ++i) {
			final String label = resultSetMetaData.getColumnLabel(i);
			if (idFieldName().equalsIgnoreCase(label)) {//id 字段不再设
				continue;
			}
//			setProperty(modelOnlyWithId, DbUtil.dbNameToJavaName(label), resultSet.getObject(i));
			setProperty(modelOnlyWithId, DbUtil.dbNameToJavaName(label), getValue(tableMeta, resultSet, i, label));
		}
		return modelOnlyWithId;
	}

	protected Object getValue(final TableMeta tableMeta, final ResultSet resultSet, final int i, final String label) throws SQLException {
		final DbColumn column = tableMeta.getColumn(label);
		if (column == null) {
			return resultSet.getObject(i);
		}
		final FieldType type = column.getType();
		if (type == null) {
			return resultSet.getObject(i);
		}
		final Kit<?> kit = type.getKit();
		if (kit == null) {
			return resultSet.getObject(i);
		}
		final String string = resultSet.getString(i);
		if (column.getNullable() && string == null) {
			return null;
		}
		return kit.fromString(string);
	}

//========================================= 写方法 update methods 
	//------------------------------------- 多model方法 methods about multi models
	protected int[] batchUpdate0(final String sql, final Collection<? extends T> models) {
		final SqlParameterSource[] sqlParameterSources = new SqlParameterSource[models.size()];
		int i = 0;
		for (final T model : models) {
			sqlParameterSources[i] = buildSqlParameterSource(model);
			++i;
		}
		return getJdbc().batchUpdate(sql, sqlParameterSources);
	}

	@Override
	protected int[] batchDel(final String sql, final Collection<? extends T> models) {
		return batchUpdate0(sql, models);
	}

	@Override
	protected int[] batchAdd(final String sql, final Collection<? extends T> models) {
		return batchUpdate0(sql, models);
	}

	@Override
	protected int[] batchAddOrUpdate(final String sql, final Collection<? extends T> models) {
		return batchUpdate0(sql, models);
	}

	@Override
	protected int[] batchUpdate(final String sql, final Collection<? extends T> models) {
		return batchUpdate0(sql, models);
	}

	//---------------------------------- 单model方法 single model handling method
	@Override
	protected ReturnKeyAndAffect add(final String sql, final T model) {
		final GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
		final SqlParameterSource source = buildSqlParameterSource(model);
		final int changedCount = getNamedJdbc().update(sql, source, keyHolder);
		final Number key = keyHolder.getKey();
		setProperty(model, idFieldName(), key);
		return new ReturnKeyAndAffect(key, changedCount);
	}

	private NamedParameterJdbcOperations getNamedJdbc() {
		return getJdbc();
	}

	@Override
	protected boolean addOrUpdate(final String sql, final T model) {
		final int rows = getNamedJdbc().update(sql, buildSqlParameterSource(model));
		return rows > 0;
	}

	@Override
	protected int update(final String sql, final T model) {
		return getJdbc().update(sql, buildSqlParameterSource(model));
	}

	@Override
	protected int del(final String sql, final T model) {
		return getJdbc().update(sql, buildSqlParameterSource(model));
	}

//=============================================
	protected TableMeta getTableMetaUsingModel(final T model) {
		return getTableMetaAccessor().getTableMetaUsingModelName(model.getClass().getSimpleName());
	}

	protected TableMeta getTableMetaUsingModels(final List<T> list) {
		return getTableMetaUsingModel(list.get(0));
	}

	@Override
	protected String getTableName(final T model) {
		return getTableMetaUsingModel(model).getName();
	}

	@Override
	protected List<String> getDbColumnNames(final T model) {
		return getTableMetaUsingModel(model).getColumnNames();
	}

	@Override
	protected Object getProperty(final T model, final String fieldName) {
		try {
			return PropertyUtils.getProperty(model, fieldName);
		} catch (final IllegalAccessException e) {
			e.printStackTrace();
		} catch (final InvocationTargetException e) {
			e.printStackTrace();
		} catch (final NoSuchMethodException e) {//some field in db not in bean
			e.printStackTrace();
		}
		return null;
	}

	@Override
	protected void setProperty(final T model, final String fieldName, final Object value) {
		try {
			PropertyUtils.setProperty(model, fieldName, value);
		} catch (final IllegalAccessException e) {
			e.printStackTrace();
		} catch (final InvocationTargetException e) {
			e.printStackTrace();
		} catch (final NoSuchMethodException e) {
			e.printStackTrace();
		}
	}

	@Override
	protected String placeHolderForField(final String fieldName) {
		return ":" + fieldName;
	}

	@Override
	protected String placeHolderForId(final int index) {
		return placeHolderForId() + index;
	}

	protected SqlParameterSource buildSqlParameterSource(final T model) {
		return new SqlParameterSource() {
			@Override
			public boolean hasValue(final String fieldName) {
				return getValue(fieldName) != null;
			}

			@Override
			public Object getValue(final String fieldName) throws IllegalArgumentException {
				return getProperty(model, fieldName);
			}

			@Override
			public String getTypeName(final String paramName) {
				return null;
			}

			@Override
			public int getSqlType(final String paramName) {
				return Integer.MIN_VALUE;
			}
		};
	}

//======================================= getters & setters
	public TableMetaAccessor getTableMetaAccessor() {
		return tableMetaAccessor;
	}

	public void setTableMetaAccessor(final TableMetaAccessor tableMetaAccessor) {
		this.tableMetaAccessor = tableMetaAccessor;
	}

	public NamedParameterJdbcOperations getJdbc() {
		return jdbc;
	}

	public void setJdbc(final NamedParameterJdbcOperations jdbc) {
		this.jdbc = jdbc;
	}
}
